package com.example.booksystem.mapper;

import com.example.booksystem.model.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserMapper {
    public static final MySQL MYSQL = new MySQL();
    private static final String GET_USER = "select id,username,password,usertype from user where username = ?";
    private static final String INSERT_USER = "insert into user (username,password,usertype) values(?,?,2)";
    private static final String SELECT_ALL_USER = "select id,username,usertype from user";
    private static final String UPDATE_USER_TO_ADMIN = "update user set usertype = 1 where id = ?";
    private static final String UPDATE_USER_TO_NOMAL = "update user set usertype = 2 where id = ?";
    private static final String DELETE_USER = "delete from user where id = ?";
    public User getUser(String username) throws SQLException {
        MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(GET_USER);
        statement.setString(1,username);
        ResultSet set = statement.executeQuery();
        while (set.next()){
            String u = set.getString("username");
            String password = set.getString("password");
            Integer id = set.getInt("id");
            Integer userType = set.getInt("usertype");
            User user = new User(id,u,password,userType);
            return user;
        }
        set.close();
        MYSQL.close();
        return null;
    }

    public Integer insertUser(User user) throws SQLException {
        MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(INSERT_USER);
        statement.setString(1, user.getUsername());
        statement.setString(2, user.getPassword());
        Integer n = MYSQL.excuteUpdate();
        MYSQL.close();
        return n;
    }
    public List<User> selectUser() throws SQLException {
        List<User> users = new ArrayList<>();
        MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(SELECT_ALL_USER);
        ResultSet set = statement.executeQuery();
        while (set.next()){
            String username = set.getString("username");
            Integer id = set.getInt("id");
            Integer userType = set.getInt("usertype");
            User user = new User(id,username,userType);
            users.add(user);
        }
        set.close();
        MYSQL.close();
        return users;
    }
    public Integer UserToAdmin(Integer id) throws SQLException {
        Connection connection = MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(UPDATE_USER_TO_ADMIN);
        statement.setInt(1, id);
        Integer n = MYSQL.excuteUpdate();
        MYSQL.close();
        return n;
    }
    public Integer UserToNomal(Integer id) throws SQLException {
        MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(UPDATE_USER_TO_NOMAL);
        statement.setInt(1, id);
        Integer n = MYSQL.excuteUpdate();
        MYSQL.close();
        return n;
    }

    public Integer delete(Integer id) throws SQLException {
        MYSQL.getConnection();
        PreparedStatement statement = MYSQL.getStatement(DELETE_USER);
        statement.setInt(1, id);
        Integer n = MYSQL.excuteUpdate();
        MYSQL.close();
        return n;
    }
}
